package com.gxuwz.medical.domain.member;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.gxuwz.medical.database.DbUtil;
import com.gxuwz.medical.database.DbUtil11;
import com.gxuwz.medical.exception.DbException;
import com.gxuwz.medical.exception.UserNotFoundException;

/**
 * 农合档案管理 成员基本信息
 * 
 * @author Administrator
 * 
 */
public class Member {
	private String nhzcode; // 农合证号
	private String ylzcode; // 医疗证号
	private String familycode;
	private String relation;
	private String housecode;
	private String idcard;
	private String sex;
	private String birthday;
	private String nation;
	private String health;
	private String culture;
	private String farmer;
	private String work;
	private String phone;
	private String age;
	private String occupation; // 职业
	private String name;

	public String getNhzcode() {
		return nhzcode;
	}

	public void setNhzcode(String nhzcode) {
		this.nhzcode = nhzcode;
	}

	public String getYlzcode() {
		return ylzcode;
	}

	public void setYlzcode(String ylzcode) {
		this.ylzcode = ylzcode;
	}

	public String getFamilycode() {
		return familycode;
	}

	public void setFamilycode(String familycode) {
		this.familycode = familycode;
	}

	public String getHousecode() {
		return housecode;
	}

	public void setHousecode(String housecode) {
		this.housecode = housecode;
	}

	public String getIdcard() {
		return idcard;
	}

	public void setIdcard(String idcard) {
		this.idcard = idcard;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public String getBirthday() {
		return birthday;
	}

	public void setBirthday(String birthday) {
		this.birthday = birthday;
	}

	public String getNation() {
		return nation;
	}

	public void setNation(String nation) {
		this.nation = nation;
	}

	public String getHealth() {
		return health;
	}

	public void setHealth(String health) {
		this.health = health;
	}

	public String getCulture() {
		return culture;
	}

	public void setCulture(String culture) {
		this.culture = culture;
	}

	public String getRelation() {
		return relation;
	}

	public void setRelation(String relation) {
		this.relation = relation;
	}

	public String getFarmer() {
		return farmer;
	}

	public void setFarmer(String farmer) {
		this.farmer = farmer;
	}

	public String getWork() {
		return work;
	}

	public void setWork(String work) {
		this.work = work;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public String getAge() {
		return age;
	}

	public void setAge(String age) {
		this.age = age;
	}

	public String getOccupation() {
		return occupation;
	}

	public void setOccupation(String occupation) {
		this.occupation = occupation;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Member() {
	}
	
	/**
	 * 加载农民档案信息
	 * @param jtcode
	 * @throws SQLException
	 */
	public void loadDByIdcard(String idcard)throws SQLException{
		Connection conn =null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			conn=DbUtil.getConn();
			pstmt=conn.prepareStatement("select * from t_member where idcard=?");
			int index=1;
			pstmt.setString(index, idcard);
			rs=pstmt.executeQuery();
			if(rs.next()){
				this.nhzcode = rs.getString("nhzcode");
				this.ylzcode = rs.getString("ylzcode");
				this.housecode = rs.getString("housecode");
				this.relation = rs.getString("relation");
				this.age = rs.getString("age");
				this.occupation = rs.getString("occupation");
				this.familycode = rs.getString("familycode");
				this.name = rs.getString("name");
				this.idcard = rs.getString("idcard");
				this.sex = rs.getString("sex");
				this.birthday = rs.getString("birthday");
				this.nation = rs.getString("nation");
				this.health = rs.getString("health");
				this.culture = rs.getString("culture");
				this.farmer = rs.getString("farmer");
				this.work = rs.getString("work");
				this.phone = rs.getString("phone");
			}
		}catch(SQLException e){
			throw e;
		}finally{
			DbUtil.close(rs, pstmt, conn);
		}
	}

	public Member(String nhzcode, String ylzcode, String familycode,
			String housecode, String relation, String idcard, String name,
			String sex, String birthday, String nation, String health,
			String culture, String farmer, String work, String phone,
			String age, String occupation) {
		super();
		this.nhzcode = nhzcode;
		this.ylzcode = ylzcode;
		this.familycode = familycode;
		this.housecode = housecode;
		this.relation = relation;
		this.idcard = idcard;
		this.name = name;
		this.sex = sex;
		this.birthday = birthday;
		this.nation = nation;
		this.health = health;
		this.culture = culture;
		this.farmer = farmer;
		this.work = work;
		this.phone = phone;
		this.age = age;
		this.occupation = occupation;
	}

	public Member(String idcard) throws Exception {
		this.idcard = idcard;
		try{
		load();
		getPersonByNum();
		}catch(Exception e){
			e.printStackTrace();
			throw new DbException("无法找到ID="+this.idcard+"对应慢性病信息");
		}
	}

	private void load() throws UserNotFoundException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DbUtil.getConn();
			pstmt = conn
					.prepareStatement("select * from t_member where idcard=?");
			int index = 1;
			pstmt.setString(index, idcard);
			rs = pstmt.executeQuery();
			if (rs != null && rs.next()) {
				this.nhzcode = rs.getString("nhzcode");
				this.ylzcode = rs.getString("ylzcode");
				this.housecode = rs.getString("housecode");
				this.relation = rs.getString("relation");
				this.age = rs.getString("age");
				this.occupation = rs.getString("occupation");
				this.familycode = rs.getString("familycode");
				this.name = rs.getString("name");
				this.idcard = rs.getString("idcard");
				this.sex = rs.getString("sex");
				this.birthday = rs.getString("birthday");
				this.nation = rs.getString("nation");
				this.health = rs.getString("health");
				this.culture = rs.getString("culture");
				this.farmer = rs.getString("farmer");
				this.work = rs.getString("work");
				this.phone = rs.getString("phone");
			}

		} catch (SQLException e) {
			throw new UserNotFoundException("Member with id " + idcard
					+ " could not be loaded from the database.");
		} finally {
			DbUtil.close(rs, pstmt, conn);
		}
	}
	//获取指定个人户内编号的个人信息
	private void getPersonByNum() throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			conn = DbUtil.getConn();
			pstmt = conn.prepareStatement("select * from t_member where housecode=? ");
			int index = 1;
			// 把页面传过来的treeID的值赋给占位符
			pstmt.setString(index, this.idcard);
			// 执行查询操作
			rs = pstmt.executeQuery();
			if(rs.next()){
				// 把查询到的结果赋给当前对象
				this.nhzcode = rs.getString("nhzcode");
				this.ylzcode = rs.getString("ylzcode");
				this.housecode = rs.getString("housecode");
				this.relation = rs.getString("relation");
				this.age = rs.getString("age");
				this.occupation = rs.getString("occupation");
				this.familycode = rs.getString("familycode");
				this.name = rs.getString("name");
				this.idcard = rs.getString("idcard");
				this.sex = rs.getString("sex");
				this.birthday = rs.getString("birthday");
				this.nation = rs.getString("nation");
				this.health = rs.getString("health");
				this.culture = rs.getString("culture");
				this.farmer = rs.getString("farmer");
				this.work = rs.getString("work");
				this.phone = rs.getString("phone");
			}
		}catch(SQLException e){
			throw new SQLException("Person with id " + this.idcard + " could not be loaded from the database.", e);
		}finally{
			DbUtil.close(rs, pstmt, conn);
		}
	}

	public void saveAndMember(String nhzcode,String housemaster, String familycode,
			String idcard, String sex, String birthday, String nation,
			String health, String culture,String relation, String farmer, String work,
			String phone, String housecode) throws SQLException {
		String name = housemaster;
		int index = -1;
		try {
			String sql = "insert into t_member(nhzcode,familycode,housecode,idcard,sex,birthday,nation,health,culture,relation,farmer,work,phone,name)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

			index = DbUtil11.executeUpdate(sql,nhzcode, familycode, housecode, idcard,
					sex, birthday, nation, health, culture,relation, farmer, work,
					phone, name);

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void editToMember(String housemaster, String familycode, String sex, String birthday, String nation,
			String health, String culture, String farmer, String work,
			String phone, String housecode,
			String idcard) throws SQLException {

		String name = housemaster;
		int index = -1;
		try {
			String sql = "update t_member set familycode=?,housecode=?,sex=?,birthday=?,nation=?,health=?,culture=?,farmer=?,work=?,phone=?,name=? where idcard=?";

			index = DbUtil11.executeUpdate(sql, familycode, housecode,
					sex, birthday, nation, health, culture, farmer, work,
					phone, name, idcard);

		} catch (SQLException e) {
			throw new SQLException("修改t_house的关联表t_member表时出错！",e);
		}
	}

	public void addMember(String ylzcode, String familycode,
			String housecode, String idcard, String name, String sex,
			String birthday, String nation, String health, String culture,
			String relation, String farmer, String work, String phone,
			String age, String occupation) throws SQLException {
		
		this.ylzcode = ylzcode;
		this.familycode = familycode;
		this.housecode =CreateHousecode(familycode);
		this.nhzcode = this.housecode;
		this.idcard = idcard;
		this.name = name;
		this.sex = sex;
		this.birthday = birthday;
		this.nation = nation;
		this.health = health;
		this.culture = culture;
		this.relation = relation;
		this.farmer = "1".equals(farmer)?"是":"否";
		this.work = work;
		this.phone = phone;
		this.age = age;
		this.occupation = occupation;

		Connection conn = null;
		try {
			conn = DbUtil.getConn();
			conn.setAutoCommit(false);
			saveToDB(conn);
			conn.commit();
		} catch (SQLException e) {
			conn.rollback();
			throw e;
		} finally {
			DbUtil.close(conn);
		}
	}
	
	/*
	 * 自动生成户主编号
	 */
	private String CreateHousecode(String familycode) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		String sql = "select max(housecode) from t_member where familycode=?";
		try {
			conn = DbUtil.getConn();
			pstmt = conn.prepareStatement(sql);
			int index = 1;
			pstmt.setString(index, this.familycode);
			rs = pstmt.executeQuery();
			String maxcode = "";
			if (rs.next()) {
				maxcode = rs.getString(1);
			}
			if (maxcode != null) {
				int beginIndex = maxcode.length() - 2;
				String no = maxcode.substring(beginIndex);
				Integer number = Integer.parseInt(no);
				++number;
				// 使用0补足位数
				no = String.format("%02d", number);
				maxcode = this.familycode + no;
			} else {
				maxcode = this.familycode + "01";
			}
			return maxcode;
		} catch (SQLException e) {

			throw new SQLException("创建父节点ID" + this.housecode + "失败!", e);
		} finally {
			DbUtil.close(rs, pstmt, conn);
		}
	}


	public String createHousecode(String familycode) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		this.familycode = familycode;
		String sql = "select max(housecode) from t_member where familycode=?";
		try {
			conn = DbUtil.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, this.familycode);
			rs = pstmt.executeQuery();
			String maxcode = "";
			if (rs.next()) {
				maxcode = rs.getString(1);
				// System.out.println("maxcode:"+maxcode);
			}
			if (maxcode != null) {
				int b = 1;
				// System.out.println("maxcode1:"+maxcode);
				Integer a = Integer.parseInt(maxcode);
				// System.out.println("a:"+a);
				int c = a + b;
				// System.out.println("c:"+c);
				String max = null;
				max = Integer.toString(c);
				maxcode = "0"+ max;
				// System.out.println("maxcode:"+maxcode);
			}
			
			return maxcode;
		} catch (SQLException e) {
			throw new SQLException("创建最大户内编号：" + this.housecode + "失败!", e);
		} finally {
			DbUtil.close(rs, pstmt, conn);
		}
	}

	protected void saveToDB(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;

	//	 System.out.println("farmer:"+farmer);
		try {
			StringBuffer sqlBuffer = new StringBuffer();
			sqlBuffer
					.append("insert into t_member(nhzcode,ylzcode,familycode,housecode,idcard,name,sex,birthday,nation,health,culture,relation,farmer,work,phone,age,occupation)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			String sql = sqlBuffer.toString();
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, this.nhzcode);
			pstmt.setString(2, this.ylzcode);
			pstmt.setString(3, this.familycode);
			pstmt.setString(4, this.housecode);
			pstmt.setString(5, this.idcard);
			pstmt.setString(6, this.name);
			pstmt.setString(7, this.sex);
			pstmt.setString(8, this.birthday);
			pstmt.setString(9, this.nation);
			pstmt.setString(10, this.health);
			pstmt.setString(11, this.culture);
			pstmt.setString(12, this.relation);
			pstmt.setString(13, this.farmer);
			pstmt.setString(14, this.work);
			pstmt.setString(15, this.phone);
			pstmt.setString(16, this.age);
			pstmt.setString(17, this.occupation);

			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("添加member表时失败！", e);
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
		}
	}

	public void editMember(String nhzcode, String ylzcode, String familycode,
			String housecode, String idcard, String name, String sex,
			String birthday, String nation, String health, String culture,
			String relation, String farmer, String work, String phone,
			String age, String occupation) throws SQLException {
		Connection conn = null;
		try {
			this.nhzcode = nhzcode;
			this.ylzcode = ylzcode;
			this.familycode = familycode;
			this.housecode = housecode;
			this.idcard = idcard;
			this.name = name;
			this.sex = sex;
			this.birthday = birthday;
			this.nation = nation;
			this.health = health;
			this.culture = culture;
			this.relation = relation;
			this.farmer = farmer;
			this.work = work;
			this.phone = phone;
			this.age = age;
			this.occupation = occupation;

			conn = DbUtil.getConn();
			conn.setAutoCommit(false);
			editToDB(conn);
			conn.commit();

		} catch (SQLException e) {
			conn.rollback();
			throw e;
		} finally {
			DbUtil.close(conn);
		}
	}

	private void editToDB(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;

		try {
			StringBuffer sqlBuff = new StringBuffer(
					"update t_member set nhzcode=?,ylzcode=?,familycode=?,housecode=?,name=?,sex=?,birthday=?,nation=?,health=?,culture=?,relation=?,farmer=?,work=?,phone=?,age=?,occupation=? where idcard=?");
			pstmt = conn.prepareStatement(sqlBuff.toString());
			pstmt.setString(1, this.nhzcode);
			pstmt.setString(2, this.ylzcode);
			pstmt.setString(3, this.familycode);
			pstmt.setString(4, this.housecode);
			pstmt.setString(5, this.name);
			pstmt.setString(6, this.sex);
			pstmt.setString(7, this.birthday);
			pstmt.setString(8, this.nation);
			pstmt.setString(9, this.health);
			pstmt.setString(10, this.culture);
			pstmt.setString(11, this.relation);
			pstmt.setString(12, this.farmer);
			pstmt.setString(13, this.work);
			pstmt.setString(14, this.phone);
			pstmt.setString(15, this.age);
			pstmt.setString(16, this.occupation);
			pstmt.setString(17, this.idcard);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("更新t_member失败!", e);
		} finally {
			DbUtil.close(pstmt);
		}
	}

	public void delcy(String idcard) throws SQLException {
		this.idcard = idcard;
		Connection conn = null;
		try {
			conn = DbUtil.getConn();
			conn.setAutoCommit(false);
			delForDB(conn);
			conn.commit();
		} catch (SQLException e) {
			conn.rollback();
			throw e;
		} finally {
			DbUtil.close(conn);
		}
	}

	protected void delForDB(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sqlBuffer = new StringBuffer();
			sqlBuffer.append("delete from t_member where idcard=?");
			pstmt = conn.prepareStatement(sqlBuffer.toString());
			pstmt.setString(1, this.idcard);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("删除t_member时出错！", e);
		} finally {
			DbUtil.close(pstmt);
		}
	}
	
	/**
	 * 家庭人口数自减
	 */
	public void countNumber1(String farmer)throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt=null;
		try{
		   conn=DbUtil.getConn();
		   String sql="update t_family set sumfamily=sumfamily-1,sumfarmer=sumfarmer+"+farmer+" where familycode=?";
		   pstmt=conn.prepareStatement(sql);
		   pstmt.setString(1, this.familycode);
		   int count=pstmt.executeUpdate();
		}catch(SQLException se){
			se.printStackTrace();
			throw se;
		}finally{
			DbUtil.close(pstmt);
		}
	} 
	
	
	
	

}
